-- @owner: zou_jialiang050
-- @date: 2023/4/12
-- @testpoint: 语句添加invoker参数,用户无function权限;部分测试点合理报错

--step1:创建用户;expect:成功
drop user if exists u_alter_006a;
drop user if exists u_alter_006b;
drop user if exists u_alter_006c;
create user u_alter_006a password 'test@1234';
create user u_alter_006b password 'test@1234';
create user u_alter_006c sysadmin password 'test@1234';
create schema sch_alter_006;
grant all on schema sch_alter_006 to u_alter_006a, u_alter_006b, u_alter_006c;

--step2:使用普通用户创建function;expect:成功
set role u_alter_006a password 'test@1234';
drop table if exists sch_alter_006.t_alter_006a;
create table sch_alter_006.t_alter_006a(c1 int);
insert into sch_alter_006.t_alter_006a values(100);
create or replace function sch_alter_006.f_alter_006a()
returns int
as $$
declare
total int;
begin
select * into total from t_alter_006a;
return total;
end;
$$ language plpgsql;
/
alter function sch_alter_006.f_alter_006a sql security invoker;

--step3:使用其他普通用户调用function;expect:合理报错
alter session set session authorization default;
alter session set session authorization u_alter_006b password 'test@1234';
call sch_alter_006.f_alter_006a();

--step4:使用系统管理员用户创建function;expect:成功
alter session set session authorization default;
alter session set session authorization u_alter_006c password 'test@1234';
drop table if exists sch_alter_006.t_alter_006c;
create table sch_alter_006.t_alter_006c(c1 int);
insert into sch_alter_006.t_alter_006c values(100);
create or replace function sch_alter_006.f_alter_006c()
returns int
as $$
declare
total int;
begin
select * into total from t_alter_006c;
return total;
end;
$$ language plpgsql;
/
alter function sch_alter_006.f_alter_006c sql security invoker;

--step5:使用普通用户调用function;expect:合理报错
alter session set session authorization default;
alter session set session authorization u_alter_006b password 'test@1234';
call sch_alter_006.f_alter_006c();

--step6:清理环境;expect:成功
alter session set session authorization default;
drop table if exists sch_alter_006.t_alter_006a;
drop table if exists sch_alter_006.t_alter_006c;
drop function if exists f_alter_006a;
drop function if exists f_alter_006c;
drop user if exists u_alter_006a cascade;
drop user if exists u_alter_006b cascade;
drop user if exists u_alter_006c cascade;
